<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

/**
 * https://my.oschina.net/u/1986301/blog/739853
 */
class DatabaseController extends Controller
{
    static private $Instance = NULL;

    static private $db = NULL;//数据库操作句柄

    static private $config = array(
        'DB_HOST' => 'localhost',
        'DB_NAME' => '',
        'DB_USER' => '',
        'DB_PWD' => '',
        'DB_PORT' => ''
    );//数据库连接配置参数

    static private $pathBC = '';  //备份还原的默认路径

    static private $error = null;   //错误信息

    static private $startTime = null;   //开始时间
    static private $finishTime = null;  //结束时间

    static private $laravelConfig = true;  //设置为true，则自动获取laravel的common/config的数据库配置

    //获取类的实例，建议不使用new 方法获取实例直接使用静态方法获取类的实例。
    static function getInstance(){
        return isset(self::$Instance) ? self::$Instance : self::$Instance = new self();
    }

    /**
     * 初始化PDO数据库连接
     * @return int 数据库实例
     */
    static function getDB()
    {
        if (!isset(self::$db)) {
            $conn = NULL;

            try {
                $dsn = 'mysql:host=' . self::$config['DB_HOST'] . ';dbname=' . self::$config['DB_NAME'];
                $conn = new \PDO($dsn, self::$config['DB_USER'], self::$config['DB_PWD'], array(\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));
                $conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            } catch (\PDOException $e) {
                self::$error = $e->getMessage();
            }

            self::$db = $conn;
        }

        return self::$db;
    }

    /**
     * 初始化函数
     * @param array $config 初始化数据库句柄,可以自行添加要连接的数据库,参数内容如上
     */
    function __construct($config = null)
    {
        if (self::$laravelConfig) self::getlaravelConfig();

        if (isset($config) && is_array($config)) self::$config = $config;

        self::getDB();
    }

    /**
     * 针对laravel自动获取相关的配置，其他的架构请自行修改
     */
    static function getlaravelConfig()
    {
        self::$config = array(
            'DB_HOST' => 'localhost',
            'DB_NAME' => env('DB_DATABASE'),
            'DB_USER' => env('DB_USERNAME'),
            'DB_PWD'  => env('DB_PASSWORD'),
            'DB_PORT' => env('DB_PORT')
        );//数据库连接配置参数

        self::$pathBC = storage_path('sql/baccarat' . date('Y-m-d', time()) . '.sql');
    }

    /**
     * 备份
     * @param string /array $table 指定表（默认为全部表）
     * @param string $path 指定路径（没有则为默认路径）
     * @return boolean
     */
    static function backup($table = null, $path = null)
    {
        self::setStartTime();//开始时间

        $_path = isset($path) ? $path : self::$pathBC;
        $_table = isset($table) ? $table : self::getTableAll();


        //而二维数组的数据，格式为:tableName(表名),DDL(定义表的代码),tableData(表的数据)
        $data = array();
        if (is_array($_table)) {
            foreach ($_table as &$value) {
                $data[] = array(
                    'tableName' => $value,
                    'DDL' => self::getDDL($value),
                    'tableData' => self::getTableData($value)
                );
            }
        } else {
            $data[] = array(
                'tableName' => $_table,
                'DDL' => self::getDDL($_table),
                'tableData' => self::getTableData($_table)
            );
        }

        self::setFinishTime();//结束时间

        $res = self::writeToFile($data, $_path);

        if ($res === false) {
            abort(404, '数据库备份失败');
        }
        echo '备份成功';
        // return back();
    }

    /**
     * 还原
     * @param string $path 指定SQL文件路径（默认为空，则寻找默认路径下的最近的备份文件）
     * @return boolean
     */
    static function restore($path = null)
    {

        self::setStartTime();//开始时间

        $_path = isset($path) ? $path : self::$pathBC;
        // echo $_path;die;
        if (!file_exists($_path)) {
            // self::$error = 'SQL文件不存在!';
            // return false;
            die('SQL文件不存在!');
        }

        //读取SQL文件
        $sql = file_get_contents($_path);
        $sql = str_replace("\r", "\n", $sql);
        $sql = explode(";\n", $sql);

        //开始安装
        foreach ($sql as $value) {
            $value = trim($value);
            if (empty($value)) continue;
            //执行sql
            if (false === self::$db->exec($value)) {
                // self::$error = '执行SQL时出现未知错误';
                // return false;
                die('执行SQL时出现未知错误');
            }
        }

        self::setFinishTime();//结束时间

        // return true;
        echo '还原成功';
    }

    /**
     * 优化表
     * @param string /array $tables 表名，默认为所有表
     * @return boolean
     * */
    static function optimize($table = null)
    {

        $_table = isset($table) ? $table : self::getTableAll();

        if (is_array($_table)) {
            foreach ($_table as &$value) {
                if (false === self::$db->query("OPTIMIZE TABLE `{$value}`")) {
                    self::$error = '执行SQL时出现未知错误';
                    return false;
                }
            }
        } else {
            if (false === self::$db->query("OPTIMIZE TABLE `{$_table}`")) {
                self::$error = '执行SQL时出现未知错误';
                return false;
            }
        }

        return true;
    }

    /**
     * 查询
     * @param string $sql
     * @return mixed
     */
    static function query($sql = null)
    {
        $stmt = self::$db->query($sql);
        $stmt->setFetchMode(\PDO::FETCH_NUM);
        $list = $stmt->fetchAll();
        return $list;
    }

    /**
     * 获取全部表
     * @return array
     */
    static function getTableAll()
    {
        $sql = 'SHOW TABLES';
        $list = self::query($sql);
        $tables = array();
        foreach ($list as $value) {
            $tables[] = $value[0];
        }
        return $tables;
    }

    /**
     * 获取表定义语句
     * @param string $table
     * @return mixed
     */
    static function getDDL($table = '')
    {
        $sql = "SHOW CREATE TABLE `{$table}`";
        $ddl = self::query($sql)[0][1] . ';';
        return $ddl;
    }

    /**
     * 获取表数据
     * @param string $table
     * @return mixed
     */
    static function getTableData($table = '')
    {
        //需要返回的SQL
        $query = '';

        $data = self::query("SELECT * FROM `{$table}`");

        foreach ($data as &$value) {
            $dataSql = '';
            foreach ($value as $v) {
                $dataSql .= "'{$v}',";
            }
            $dataSql = substr($dataSql, 0, -1);
            $query .= "INSERT INTO `{$table}` VALUES ({$dataSql});\r\n";
        }

        return $query;
    }

    /**
     * 写出到文件
     * @param array $data
     * @param string $path
     * @return boolean
     */
    static private function writeToFile($data = null, $path)
    {
        if (!isset($data) || !is_array($data)) {
            self::$error = '数据异常';
            return false;
        }
        $str = "/*\r\nMySQL Database Backup Tools\r\n";
        $str .= "Server:" . self::$config['DB_HOST'] . ":" . self::$config['DB_PORT'] . "\r\n";
        $str .= "Database:" . self::$config['DB_NAME'] . "\r\n";
        $str .= "Data:" . date('Y-m-d H:i:s', time()) . "\r\n*/\r\n";
        $str .= "SET FOREIGN_KEY_CHECKS=0;\r\n";

        foreach ($data as $value) {
            $str .= "-- ----------------------------\r\n";
            $str .= "-- Table structure for {$value['tableName']}\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= "DROP TABLE IF EXISTS `{$value['tableName']}`;\r\n";
            $str .= $value['DDL'] . "\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= "-- Records of {$value['tableName']}\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= $value['tableData'] . "\r\n";
        }

        return file_put_contents($path, $str);

    }

    /**
     * 错误信息
     * @return mixed
     */
    static function getError()
    {
        return self::$error;
    }

    /**
     * 设置开始时间
     */
    static private function setStartTime()
    {
        self::$startTime = microtime(true);
    }

    /**
     * 设置结束时间
     */
    static private function setFinishTime()
    {
        self::$finishTime = microtime(true);
    }

    /**
     * 获取备份还原用时，单位'ms'
     * @return double
     */
    static function getRunTime()
    {
        return self::$finishTime - self::$startTime;
    }

    //析构函数，类结束
    function __destruct()
    {
        self::$db = null;
        // echo '备份成功';
    }
}
